package com.bcu.cn.onlineexamsystem.adminmanage.mapper;

import com.bcu.cn.onlineexamsystem.adminmanage.entity.AdminUser;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface AdminUserMapper {

    @Select("SELECT u.user_id, u.user_name, u.password, u.role, u.email, u.phone, u.real_name, u.class_id, c.class_name " +
            "FROM users u LEFT JOIN classes c ON u.class_id = c.class_id " +
            "WHERE u.role IN ('student', 'teacher')")
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "userName", column = "user_name"),
            @Result(property = "role", column = "role"),
            @Result(property = "email", column = "email"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "realName", column = "real_name"),
            @Result(property = "classId", column = "class_id"),
            @Result(property = "className", column = "class_name")
    })
    List<AdminUser> findAllUsers();

    @Select("SELECT u.user_id, u.user_name, u.password, u.role, u.email, u.phone, u.real_name, u.class_id, c.class_name " +
            "FROM users u LEFT JOIN classes c ON u.class_id = c.class_id " +
            "WHERE u.user_id = #{userId}")
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "userName", column = "user_name"),
            @Result(property = "role", column = "role"),
            @Result(property = "email", column = "email"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "realName", column = "real_name"),
            @Result(property = "classId", column = "class_id"),
            @Result(property = "className", column = "class_name")
    })
    AdminUser findUserById(String userId);

    @Insert("INSERT INTO users (user_id, user_name, password, role, email, phone, real_name, class_id) " +
            "VALUES (#{userId}, #{userName}, #{password}, #{role}, #{email}, #{phone}, #{realName}, #{classId})")
    int insertUser(AdminUser user);

    @Update("UPDATE users SET " +
            "user_name = #{userName}, " +
            "email = #{email}, " +
            "phone = #{phone}, " +
            "real_name = #{realName}, " +
            "class_id = #{classId} " +
            "WHERE user_id = #{userId}")
    int updateUser(AdminUser user);

    @Delete("DELETE FROM users WHERE user_id = #{userId}")
    int deleteUser(String userId);

    @Select({
        "<script>",
        "SELECT u.user_id, u.user_name, u.password, u.role, u.email, u.phone, u.real_name, u.class_id, c.class_name ",
        "FROM users u LEFT JOIN classes c ON u.class_id = c.class_id ",
        "WHERE u.role IN ('student', 'teacher')",
        "<if test='keyword != null and keyword != \"\"'>",
        "AND (u.user_name LIKE CONCAT('%', #{keyword}, '%') ",
        "OR u.email LIKE CONCAT('%', #{keyword}, '%') ",
        "OR u.real_name LIKE CONCAT('%', #{keyword}, '%'))",
        "</if>",
        "<if test='role != null and role != \"\"'>",
        "AND u.role = #{role}",
        "</if>",
        "</script>"
    })
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "userName", column = "user_name"),
            @Result(property = "role", column = "role"),
            @Result(property = "email", column = "email"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "realName", column = "real_name"),
            @Result(property = "classId", column = "class_id"),
            @Result(property = "className", column = "class_name")
    })
    List<AdminUser> searchUsers(@Param("keyword") String keyword, @Param("role") String role);

    // 用户总数
    @Select("SELECT COUNT(*) FROM users")
    int countAllUsers();

    // 按角色统计
    @Select("SELECT COUNT(*) FROM users WHERE role = #{role}")
    int countByRole(@Param("role") String role);

    // 班级总数
    @Select("SELECT COUNT(*) FROM classes")
    int countAllClasses();

    // 按日期统计注册用户数（假设create_time为注册时间）
    @Select("SELECT COUNT(*) FROM users WHERE DATE(create_time) = #{dateStr}")
    int countUsersByDate(@Param("dateStr") String dateStr);

    // 查询所有老师
    @Select("SELECT * FROM users WHERE role = 'teacher'")
    List<com.bcu.cn.onlineexamsystem.user.entity.User> findAllTeachers();

    // 查询老师已分配课程ID
    @Select("SELECT course_id FROM teacher_courses WHERE teacher_id = #{teacherId}")
    List<Long> findCourseIdsByTeacher(@Param("teacherId") String teacherId);

    // 查询老师已分配班级ID
    @Select("SELECT class_id FROM teacher_classes WHERE teacher_id = #{teacherId}")
    List<Long> findClassIdsByTeacher(@Param("teacherId") String teacherId);

    // 删除老师所有课程分配
    @Delete("DELETE FROM teacher_courses WHERE teacher_id = #{teacherId}")
    void deleteTeacherCourses(@Param("teacherId") String teacherId);

    // 批量插入老师课程分配
    @Insert({
        "<script>",
        "INSERT INTO teacher_courses (teacher_id, course_id, assign_time) VALUES",
        "<foreach collection='courseIds' item='cid' separator=','>",
        "(#{teacherId}, #{cid}, NOW())",
        "</foreach>",
        "</script>"
    })
    void batchInsertTeacherCourses(@Param("teacherId") String teacherId, @Param("courseIds") List<Long> courseIds);

    // 删除老师所有班级分配
    @Delete("DELETE FROM teacher_classes WHERE teacher_id = #{teacherId}")
    void deleteTeacherClasses(@Param("teacherId") String teacherId);

    // 批量插入老师班级分配
    @Insert({
        "<script>",
        "INSERT INTO teacher_classes (teacher_id, class_id, assign_time) VALUES",
        "<foreach collection='classIds' item='cid' separator=','>",
        "(#{teacherId}, #{cid}, NOW())",
        "</foreach>",
        "</script>"
    })
    void batchInsertTeacherClasses(@Param("teacherId") String teacherId, @Param("classIds") List<Long> classIds);

    // 查询学生已分配课程ID
    @Select("SELECT course_id FROM student_courses WHERE student_id = #{studentId}")
    List<Long> findCourseIdsByStudent(@Param("studentId") String studentId);

    // 删除学生所有课程分配
    @Delete("DELETE FROM student_courses WHERE student_id = #{studentId}")
    void deleteStudentCourses(@Param("studentId") String studentId);

    // 批量插入学生课程分配
    @Insert({
        "<script>",
        "INSERT INTO student_courses (student_id, course_id, enroll_time) VALUES",
        "<foreach collection='courseIds' item='cid' separator=','>",
        "(#{studentId}, #{cid}, NOW())",
        "</foreach>",
        "</script>"
    })
    void batchInsertStudentCourses(@Param("studentId") String studentId, @Param("courseIds") List<Long> courseIds);
}